<?php
/**
 * Created by PhpStorm.
 * User: lingyun
 * Date: 2018/6/14
 * Time: 8:30
 * Desc: 销售统计 -- 直营店销售统计
 */
namespace app\kuguan\controller;
use controller\BasicAdmin;
use service\DataService;
use think\Db;
use PHPExcel;
use PHPExcel_IOFactory;

class Sales extends BasicAdmin{
    /**
     * @Author  lingyun
     * @Desc    工厂销售统计 -- 直营店
     * 2018-06-14
     */
    public function index(){
        $get = request()->get();

        $map['is_diannei'] = ['eq',1];
        $store_id = db('store')->where(['is_deleted'=>0,'is_jiameng'=>0])->column('id');
        if(empty($get['date'])){            //近看本月的小粉统计
            $time = $this->get_month_time();

            $map1 = $this->get_map(1,$time,$store_id);        //支付宝 -- 订单
            $map2 = $this->get_map(2,$time,$store_id);        //微信 -- 订单
            $map3 = $this->get_map(3,$time,$store_id);        //余额 -- 订单
            $map4 = $this->get_map(4,$time,$store_id);        //现金 -- 订单
            $map5 = $this->get_map(5,$time,$store_id);        //银行卡 -- 订单
            $map6 = $this->get_map(6,$time,$store_id);        //美团 -- 订单
            $map7 = $this->get_map(7,$time,$store_id);        //饿了么 -- 订单

            $map_r = $this->get_recharge_map($time,$store_id);          //会员充值条件
            $map_coupon = $this->get_coupon_map($time,$store_id);       //优惠券金额
            $map_cash_coupon = $this->get_cash_coupon_map($time,$store_id);  //代金券金额
            $map_back_money = $this->get_back_map($time,$store_id);          //订单退款金额

            $ali_amount = db('pay_log')->where($map1)->sum('amount');
            $wx_amount = db('pay_log')->where($map2)->sum('amount');
            $yue_amount = db('pay_log')->where($map3)->sum('amount');
            $cash_amount = db('pay_log')->where($map4)->sum('amount');
            $bank_amount = db('pay_log')->where($map5)->sum('amount');
            $meituan_amount = db('pay_log')->where($map6)->sum('amount');
            $elema_amount = db('pay_log')->where($map7)->sum('amount');
            $recharge = db('pay_log')->where($map_r)->sum('amount');
            $coupon_amount = db('order')->where($map_coupon)->sum('coupon_price');
            $cash_coupon_amount = db('order')->where($map_cash_coupon)->sum('cash_coupon_price');
            $back_amount = db('pay_log')->where($map_back_money)->sum('amount');

            $ali_amount = $ali_amount==0?0:$ali_amount;
            $wx_amount = $wx_amount==0?0:$wx_amount;
            $yue_amount = $yue_amount==0?0:$yue_amount;
            $cash_amount = $cash_amount==0?0:$cash_amount;
            $bank_amount = $bank_amount==0?0:$bank_amount;
            $meituan_amount = $meituan_amount==0?0:$meituan_amount;
            $elema_amount = $elema_amount==0?0:$elema_amount;
            $recharge = $recharge==0?0:$recharge;
            $coupon_amount = $coupon_amount==0?0:$coupon_amount;
            $cash_coupon_amount = $cash_coupon_amount==0?0:$cash_coupon_amount;
            $back_amount = $back_amount==0?0:$back_amount;

            $final_amount = $ali_amount+$wx_amount+$yue_amount+$cash_amount+$bank_amount+$meituan_amount+$elema_amount+$recharge;

            $this->assign('title','直营店销售统计');
            $this->assign('ali_amount',$ali_amount);
            $this->assign('wx_amount',$wx_amount);
            $this->assign('yue_amount',$yue_amount);
            $this->assign('cash_amount',$cash_amount);
            $this->assign('bank_amount',$bank_amount);
            $this->assign('meituan_amount',$meituan_amount);
            $this->assign('elema_amount',$elema_amount);
            $this->assign('recharge',$recharge);
            $this->assign('coupon_amount',$coupon_amount);
            $this->assign('cash_coupon_amount',$cash_coupon_amount);
            $this->assign('back_amount',$back_amount);
            $this->assign('final_amount',$final_amount);

            return $this->fetch();
        }else{
            $date = input('date');
            list($start, $end) = explode('-', str_replace(' ', '', $date));
            $start = $start."  00:00:00";		$end = $end." 23:59:59";
            $start = strtotime($start);			$end = strtotime($end);
            $time['start'] = $start;
            $time['end'] = $end;

            $map1 = $this->get_map(1,$time,$store_id);        //支付宝 -- 订单
            $map2 = $this->get_map(2,$time,$store_id);        //微信 -- 订单
            $map3 = $this->get_map(3,$time,$store_id);        //余额 -- 订单
            $map4 = $this->get_map(4,$time,$store_id);        //现金 -- 订单
            $map5 = $this->get_map(5,$time,$store_id);        //银行卡 -- 订单
            $map6 = $this->get_map(6,$time,$store_id);        //美团 -- 订单
            $map7 = $this->get_map(7,$time,$store_id);        //饿了么 -- 订单

            $map_r = $this->get_recharge_map($time,$store_id);          //会员充值条件
            $map_coupon = $this->get_coupon_map($time,$store_id);       //优惠券金额
            $map_cash_coupon = $this->get_cash_coupon_map($time,$store_id);  //代金券金额
            $map_back_money = $this->get_back_map($time,$store_id);          //订单退款金额

            $ali_amount = db('pay_log')->where($map1)->sum('amount');
            $wx_amount = db('pay_log')->where($map2)->sum('amount');
            $yue_amount = db('pay_log')->where($map3)->sum('amount');
            $cash_amount = db('pay_log')->where($map4)->sum('amount');
            $bank_amount = db('pay_log')->where($map5)->sum('amount');
            $meituan_amount = db('pay_log')->where($map6)->sum('amount');
            $elema_amount = db('pay_log')->where($map7)->sum('amount');
            $recharge = db('pay_log')->where($map_r)->sum('amount');
            $coupon_amount = db('order')->where($map_coupon)->sum('coupon_price');
            $cash_coupon_amount = db('order')->where($map_cash_coupon)->sum('cash_coupon_price');
            $back_amount = db('pay_log')->where($map_back_money)->sum('amount');

            $ali_amount = $ali_amount==0?0:$ali_amount;
            $wx_amount = $wx_amount==0?0:$wx_amount;
            $yue_amount = $yue_amount==0?0:$yue_amount;
            $cash_amount = $cash_amount==0?0:$cash_amount;
            $bank_amount = $bank_amount==0?0:$bank_amount;
            $meituan_amount = $meituan_amount==0?0:$meituan_amount;
            $elema_amount = $elema_amount==0?0:$elema_amount;
            $recharge = $recharge==0?0:$recharge;
            $coupon_amount = $coupon_amount==0?0:$coupon_amount;
            $cash_coupon_amount = $cash_coupon_amount==0?0:$cash_coupon_amount;
            $back_amount = $back_amount==0?0:$back_amount;

            $final_amount = $ali_amount+$wx_amount+$yue_amount+$cash_amount+$bank_amount+$meituan_amount+$elema_amount+$recharge;

            $this->assign('title','直营店销售统计');
            $this->assign('ali_amount',$ali_amount);
            $this->assign('wx_amount',$wx_amount);
            $this->assign('yue_amount',$yue_amount);
            $this->assign('cash_amount',$cash_amount);
            $this->assign('bank_amount',$bank_amount);
            $this->assign('meituan_amount',$meituan_amount);
            $this->assign('elema_amount',$elema_amount);
            $this->assign('recharge',$recharge);
            $this->assign('coupon_amount',$coupon_amount);
            $this->assign('cash_coupon_amount',$cash_coupon_amount);
            $this->assign('back_amount',$back_amount);
            $this->assign('final_amount',$final_amount);

            return $this->fetch();
        }
    }

    /**
     * @Author  lingyun
     * @Desc    获取本月时间
     * 2018-06-14
     */
    private function get_month_time(){
        $begin_time = strtotime(date("Y-m-d H:i:s", mktime ( 0, 0, 0, date ( "m" ), 1, date ( "Y" ) ) ));
        $end_time = strtotime(date("Y-m-d H:i:s", mktime ( 23, 59, 59, date ( "m" ), date ( "t" ), date ( "Y" ) ) ));

        return ['start'=>$begin_time,'end'=>$end_time];
    }

    /**
     * @Author  lingyun
     * @Desc    创建查询条件
     * @param int $sign 选项类型
     */
    private function get_map($sign = 1,$time,$store_id){
        $map['pay_type'] = ['eq',$sign];
        $map['pay_sign'] = ['in','1,3'];
        $map['store_id'] = ['in',$store_id];
        $map['addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');

        return $map;
    }

    /**
     * @Author  lingyun
     * @Desc    创建会员充值条件
     * @param $time
     */
    private function get_recharge_map($time,$store_id){
        $map['pay_sign'] = ['eq',2];
//        $map['store_id'] = ['in',$store_id];
        $map['addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');

        return $map;
    }

    /**
     * @Author  lingyun
     * @Desc    查询优惠券价格
     *
     */
    private function get_coupon_map($time,$store_id){
        $map['pay_status'] = ['eq',1];
        $map['store_id'] = ['in',$store_id];
        $map['order_status'] = ['not in','0,5,6,7,8'];
        $map['coupon_id'] = ['neq',0];
        $map['addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');

        return $map;
    }

    /**
     * @Author  lingyun
     * @Desc    获取现金券价格
     * 2018-06-16
     */
    private function get_cash_coupon_map($time,$store_id){
        $map['pay_status'] = ['eq',1];
        $map['store_id'] = ['in',$store_id];
        $map['order_status'] = ['not in','0,5,6,7,8'];
        $map['cash_coupon_id'] = ['neq',0];
        $map['addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');

        return $map;
    }

    /**
     * @Author  lingyun
     * @Desc    查询退款金额条件
     * 2018-06-16
     */
    private function get_back_map($time,$store_id){
        $map['pay_sign'] = ['eq',4];
        $map['store_id'] = ['in',$store_id];
        $map['addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');
        return $map;
    }

    /**
     * @Author  lingyun
     * @Desc    导出表格信息
     * 2018-06-17
     */
    public function export(){
        $get = request()->get();

        $map['is_diannei'] = ['eq',1];
        $store_id = db('store')->where('is_jiameng',0)->column('id');
        if(empty($get['date'])){            //近看本月的小粉统计

            $time = $this->get_month_time();
            $map1 = $this->get_map(1,$time,$store_id);        //支付宝 -- 订单
            $map2 = $this->get_map(2,$time,$store_id);        //微信 -- 订单
            $map3 = $this->get_map(3,$time,$store_id);        //余额 -- 订单
            $map4 = $this->get_map(4,$time,$store_id);        //现金 -- 订单
            $map5 = $this->get_map(5,$time,$store_id);        //银行卡 -- 订单
            $map6 = $this->get_map(6,$time,$store_id);        //美团 -- 订单
            $map7 = $this->get_map(7,$time,$store_id);        //饿了么 -- 订单

            $map_r = $this->get_recharge_map($time,$store_id);          //会员充值条件
            $map_coupon = $this->get_coupon_map($time,$store_id);       //优惠券金额
            $map_cash_coupon = $this->get_cash_coupon_map($time,$store_id);  //代金券金额
            $map_back_money = $this->get_back_map($time,$store_id);          //订单退款金额

            $ali_amount = db('pay_log')->where($map1)->sum('amount');
            $wx_amount = db('pay_log')->where($map2)->sum('amount');
            $yue_amount = db('pay_log')->where($map3)->sum('amount');
            $cash_amount = db('pay_log')->where($map4)->sum('amount');
            $bank_amount = db('pay_log')->where($map5)->sum('amount');
            $meituan_amount = db('pay_log')->where($map6)->sum('amount');
            $elema_amount = db('pay_log')->where($map7)->sum('amount');
            $recharge = db('pay_log')->where($map_r)->sum('amount');
            $coupon_amount = db('order')->where($map_coupon)->sum('coupon_price');
            $cash_coupon_amount = db('order')->where($map_cash_coupon)->sum('cash_coupon_price');
            $back_amount = db('pay_log')->where($map_back_money)->sum('amount');

            $ali_amount = $ali_amount==0?0:$ali_amount;
            $wx_amount = $wx_amount==0?0:$wx_amount;
            $yue_amount = $yue_amount==0?0:$yue_amount;
            $cash_amount = $cash_amount==0?0:$cash_amount;
            $bank_amount = $bank_amount==0?0:$bank_amount;
            $meituan_amount = $meituan_amount==0?0:$meituan_amount;
            $elema_amount = $elema_amount==0?0:$elema_amount;
            $recharge = $recharge==0?0:$recharge;
            $coupon_amount = $coupon_amount==0?0:$coupon_amount;
            $cash_coupon_amount = $cash_coupon_amount==0?0:$cash_coupon_amount;
            $back_amount = $back_amount==0?0:$back_amount;

            $final_amount = $ali_amount+$wx_amount+$yue_amount+$cash_amount+$bank_amount+$meituan_amount+$elema_amount+$recharge;


        }else{
            $date = input('date');
            list($start, $end) = explode('-', str_replace(' ', '', $date));
            $start = $start."  00:00:00";		$end = $end." 23:59:59";
            $start = strtotime($start);			$end = strtotime($end);
            $time['start'] = $start;
            $time['end'] = $end;

            $map1 = $this->get_map(1,$time,$store_id);        //支付宝 -- 订单
            $map2 = $this->get_map(2,$time,$store_id);        //微信 -- 订单
            $map3 = $this->get_map(3,$time,$store_id);        //余额 -- 订单
            $map4 = $this->get_map(4,$time,$store_id);        //现金 -- 订单
            $map5 = $this->get_map(5,$time,$store_id);        //银行卡 -- 订单
            $map6 = $this->get_map(6,$time,$store_id);        //美团 -- 订单
            $map7 = $this->get_map(7,$time,$store_id);        //饿了么 -- 订单

            $map_r = $this->get_recharge_map($time,$store_id);          //会员充值条件
            $map_coupon = $this->get_coupon_map($time,$store_id);       //优惠券金额
            $map_cash_coupon = $this->get_cash_coupon_map($time,$store_id);  //代金券金额
            $map_back_money = $this->get_back_map($time,$store_id);          //订单退款金额

            $ali_amount = db('pay_log')->where($map1)->sum('amount');
            $wx_amount = db('pay_log')->where($map2)->sum('amount');
            $yue_amount = db('pay_log')->where($map3)->sum('amount');
            $cash_amount = db('pay_log')->where($map4)->sum('amount');
            $bank_amount = db('pay_log')->where($map5)->sum('amount');
            $meituan_amount = db('pay_log')->where($map6)->sum('amount');
            $elema_amount = db('pay_log')->where($map7)->sum('amount');
            $recharge = db('pay_log')->where($map_r)->sum('amount');
            $coupon_amount = db('order')->where($map_coupon)->sum('coupon_price');
            $cash_coupon_amount = db('order')->where($map_cash_coupon)->sum('cash_coupon_price');
            $back_amount = db('pay_log')->where($map_back_money)->sum('amount');

            $ali_amount = $ali_amount==0?0:$ali_amount;
            $wx_amount = $wx_amount==0?0:$wx_amount;
            $yue_amount = $yue_amount==0?0:$yue_amount;
            $cash_amount = $cash_amount==0?0:$cash_amount;
            $bank_amount = $bank_amount==0?0:$bank_amount;
            $meituan_amount = $meituan_amount==0?0:$meituan_amount;
            $elema_amount = $elema_amount==0?0:$elema_amount;
            $recharge = $recharge==0?0:$recharge;
            $coupon_amount = $coupon_amount==0?0:$coupon_amount;
            $cash_coupon_amount = $cash_coupon_amount==0?0:$cash_coupon_amount;
            $back_amount = $back_amount==0?0:$back_amount;

            $final_amount = $ali_amount+$wx_amount+$yue_amount+$cash_amount+$bank_amount+$meituan_amount+$elema_amount+$recharge;
        }

        $filename = '直营店销售统计'.date('YmdHis').'.xls';

        $objPHPExcel = new PHPExcel();//新建一个表
        $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
        $objSheet->setTitle("直营店销售统计");
        $title = '直营店销售统计 - '.$get['date'];

        $objSheet->setCellValue('A1', $title);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');      //合并
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(     //加粗和居中
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );


        $objSheet
            ->setCellValue('A2', '支付宝')
            ->setCellValue('B2', '微信')
            ->setCellValue('C2', '会员卡')
            ->setCellValue('D2', '现金')
            ->setCellValue('E2', '银行')
            ->setCellValue('F2', '美团')
            ->setCellValue('G2', '饿了么')
            ->setCellValue('H2', '充值')
            ->setCellValue('I2', '优惠券')
            ->setCellValue('J2', '代金券')
            ->setCellValue('K2', '退款')
            ->setCellValue('L2', '总计');


        $j = 3;     //从第三行开始
        $objSheet
            ->setCellValue('A' . $j, $ali_amount)
            ->setCellValue('B' . $j, $wx_amount)
            ->setCellValue('C' . $j, $yue_amount)
            ->setCellValue('D' . $j, $cash_amount)
            ->setCellValue('E' . $j, $bank_amount)
            ->setCellValue('F' . $j, $meituan_amount)
            ->setCellValue('G' . $j, $elema_amount)
            ->setCellValue('H' . $j, $recharge)
            ->setCellValue('I' . $j, $coupon_amount)
            ->setCellValue('J' . $j, $cash_coupon_amount)
            ->setCellValue('K' . $j, $back_amount)
            ->setCellValue('L' . $j, $final_amount);
        $j ++;

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $objWriter->save("php://output");

    }

}
